const express = require('express');
const app = express();
let url = require('url');
//跨域请求
app.all('*', function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "content-type,content-length, authorization,\'origin\',X-Requested-With");
    res.header("Access-Control-Allow-Methods","PUT,POST,GET,DELETE,OPTIONS");
    res.header('Access-Control-Allow-Credentials', true);
    res.header("X-Powered-By",' 3.2.1')
    res.header("Content-Type", "application/json;charset=utf-8");
    next();
});

//添加数据库模板依赖
const models = require('../../db/models');
const bodyParser = require('body-parser');
const path = require("path");
//时间格式设置所需要组件
//引入组件
app.use(bodyParser.urlencoded({extended:false}));
app.use(bodyParser.json());
app.use(express.static(path.join(__dirname,'public')));

//页面的入口，跳转到index页面，然后通过钩子函数，查询数据渲染页面
app.get('/login',function (req,res){
    res.type("html");
    res.sendFile("D:/Workspaces/IdeaWorkspaces/StudentSys/src/index.html");
})
//跳转course.html页面
app.get('/toCourse',function (req,res) {
    res.type("html");
    res.sendFile("D:/Workspaces/IdeaWorkspaces/StudentSys/src/course.html");
})
//跳转choice.html页面
app.get('/toChoice',function (req,res) {
    res.type("html");
    res.sendFile("D:/Workspaces/IdeaWorkspaces/StudentSys/src/choice.html");
})
//刷新choice.html页面数据
app.post('/show',async function (req,res){
    let list = await models.stu_course.findAll();
    res.json({
        list,
    })
})
//跳转detail.html页面
app.get('/toDetail',function (req,res) {
    res.type("html");
    res.sendFile("D:/Workspaces/IdeaWorkspaces/StudentSys/src/detail.html");
})
//根据id查学生信息，与对应的课程
app.post('/query',async function (req,res){
    let id = req.body.data.sid;
    let retObj = await models.stu_student.findOne({
        attributes:{
            include:[
                //[models.Sequelize.fn('date_format', models.Sequelize.col('entertime'), '%Y-%m-%d %H:%i:%s'), 'entertime'],
                [models.Sequelize.fn('date_format', models.Sequelize.col('entertime'), '%Y-%m-%d'), 'entertime'],
            ],
        },
        where:{
            id
        },
    });
    let retJoin = [];
    if (retObj != null){
        retJoin = await models.sequelize.query("SELECT s.id sid,c.id cid,c.name,c.credit,c.period,c.remark from stu_students s INNER JOIN studentjoincourses j on s.id=j.sid inner JOIN stu_courses c on j.cid=c.id where s.id=?",{
            replacements:[id],
            type:models.Sequelize.QueryTypes.SELECT
        })
        /*models.stu_student.belongsToMany(models.studentjoincourse,{foreignKey:'id',targetKey:'sid'});
        let retJoin = models.stu_student.findOne({
            attributes:{
                include:[
                    //[models.Sequelize.fn('date_format', models.Sequelize.col('entertime'), '%Y-%m-%d %H:%i:%s'), 'entertime'],
                    [models.Sequelize.fn('date_format', models.Sequelize.col('entertime'), '%Y-%m-%d'), 'entertime'],
                ],
            },
            where:{
                id
            },
        })*/
    }
    res.json({
        retObj,
        retJoin
    })
})
//根据id查询课程信息
app.post("/findById",async function (req,res) {
    let id = req.body.data.cid;
    let retObj = await models.stu_course.findOne({
        //为属性取别名
        attributes:["id","name","credit","period","remark",["id","cid"]],
        where:{
            id
        },
    });
    res.json({
        retObj
    })
})
//根据id数组删除
app.post('/delete',async function (req, res) {
    //如果查询有数据，则可以删除
    let cid = req.body.data.cid;
    let sid = req.body.data.sid;
    let one = await models.studentjoincourse.findOne({
        where:{
            cid,sid
        }
    });
    if (one != null){
        await models.studentjoincourse.destroy({
            where:{
                cid,sid
            }
        })
    }
    //重新查询数据该学生对应的课程信息返回页面
    let retJoin = [];
    retJoin = await models.sequelize.query("SELECT s.id sid,c.id cid,c.name,c.credit,c.period,c.remark from stu_students s INNER JOIN studentjoincourses j on s.id=j.sid inner JOIN stu_courses c on j.cid=c.id where s.id=?",{
        replacements:[sid],
        type:models.Sequelize.QueryTypes.SELECT
    })
    res.json({
        retJoin
    })
})
//添加学生课程中间表数据
app.post('/save',async function (req,res){
    let sid = req.body.data.sid;
    let listCid = req.body.data.listCid;
    for (index in listCid){
        models.studentjoincourse.create({
            sid,
            cid:listCid[index].cid
        })
    }
    res.send({})
})
//异常处理
function error_handler(err,req,res,next){
    if (err){
        let {message} = err;
        res.status(500).json({
            message:message || "服务器异常"
        })
    }
}

app.use(error_handler);

//服务监听
app.listen(3002,()=>{
    console.log("启动成功choice!")
})